﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_BackDocument]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_BackDocument];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_BackDocument]
    @DocID int
AS
SET NOCOUNT ON
DECLARE @order_id     INT
DECLARE @step_id     INT
DECLARE @flow_id     INT
DECLARE @msgid         INT
DECLARE @staffname    nvarchar(100)

SET @staffname = ''
SELECT     @order_id= MAX(ORDER_ID) 
    FROM 
        UDS_FLOW_PATH 
    WHERE     DOC_ID = @docid
IF @order_id>1
BEGIN
    --删除上次提交人员备份
    DELETE     FROM     uds_flow_path 
        WHERE     doc_id         = @docid
            and order_id     = @order_id
    --删除上次人员
    DELETE     FROM     uds_flow_status 
        WHERE     doc_id         = @docid            

    --恢复上次提交人员
    INSERT INTO     uds_flow_status (doc_id,staff_id,status)
    SELECT @docid,staff_id,0
        FROM 
            uds_flow_path
        WHERE     doc_id        = @docid
            and order_id     = @order_id -1 
    --获得当前流程和步骤
    SELECT     @flow_id         = flow_id ,
        @step_id         = step_id
        FROM     uds_flow_path
        WHERE     doc_id        = @docid
            and order_id     = @order_id -1 

    --获得最后一个回退人姓名
    SELECT     TOP 1 @staffname = b.realname
        FROM     uds_flow_postil a,
            uds_staff b
        WHERE     a.postiler_id = b.staff_id
            and a.doc_id = @docid
            and a.postil_type = 2
        ORDER BY postil_date DESC
    --回退提醒
    INSERT INTO uds_sms_msg
        (sender,content,sendtime,type)
        VALUES(@staffname,'有新公文被退回！',getdate(),1)
    SET @msgid = IDENT_CURRENT('uds_sms_msg')
    INSERT INTO uds_sms_receiver
        (msgid,receiver,mobileno,type,isread)
        SELECT     @msgid,
            b.staff_name,
            b.mobile,
            1,0                        
            FROM 
                uds_flow_path a,
                uds_staff b
            WHERE 
                a.staff_id     = b.staff_id
                and a.doc_id    = @docid
                and a.order_id     = @order_id -1 


    --删除真正的消息
    DELETE FROM UDS_SMS_MobileMsgSendBuffer        
        WHERE     msgid in (
                    SELECT     msgid 
                        FROM 
                            uds_flow_message
                        WHERE     doc_id = @docid
                            and flow_id = @flow_id
                            and step_id = @step_id            

                  )
        
    --删除当前步骤的提醒
    DELETE FROM uds_flow_message
        WHERE     doc_id = @docid
            and flow_id = @flow_id
            and step_id = @step_id            

    --更新文档状态
    UPDATE     uds_flow_document
        SET step_id     = @step_id,
            flow_id     = @flow_id,
            doc_status     = 0
        WHERE DOC_ID = @docid    
    IF @step_id = 1
    BEGIN
        UPDATE uds_flow_document
            SET isRunning = 0
            WHERE DOC_ID = @docid        
    END

SET NOCOUNT OFF
END